﻿using System.Collections.Generic;

namespace Netnr.DataKit.DK
{
    /// <summary>
    /// 实现接口
    /// </summary>
    public class DataKitSQLServer : IDataKit
    {
        /// <summary>
        /// 获取所有表信息的SQL脚本
        /// </summary>
        public string GetTableSQL = @"
                                        SELECT
                                          a.name AS TableName,
                                          b.value AS TableComment
                                        FROM
                                          sys.TABLES a
                                          left join sys.extended_properties b ON b.major_id = a.object_id
                                        WHERE
                                          b.minor_id = 0
                                        ORDER BY a.name
                                    ";

        /// <summary>
        /// 获取所有列信息的SQL脚本
        /// </summary>
        public string GetColumnSQL = @"
                                        SELECT TableName = d.name,
                                               TableComment = ISNULL(f.value, ''),
                                               FieldName = a.name,
                                               DataTypeLength = b.name + '(' + CONVERT(VARCHAR(10), COLUMNPROPERTY(a.id, a.name, 'PRECISION')) + ')',
                                               DataType = b.name,
                                               [DataLength] = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),
                                               DataScale = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0),
                                               FieldOrder = a.colorder,
                                               PrimaryKey = CASE
                                                                WHEN EXISTS
                                                                     (
                                                                         SELECT 1
                                                                         FROM sysobjects
                                                                         WHERE xtype = 'PK'
                                                                               AND name IN
                                                                                   (
                                                                                       SELECT name
                                                                                       FROM sysindexes
                                                                                       WHERE indid IN
                                                                                             (
                                                                                                 SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid
                                                                                             )
                                                                                   )
                                                                     ) THEN
                                                                    'YES'
                                                                ELSE
                                                                    ''
                                                            END,
                                               AutoAdd = CASE
                                                             WHEN i.name IS NULL THEN
                                                                 ''
                                                             ELSE
                                                                 'YES'
                                                         END,
                                               NotNull = CASE
                                                             WHEN a.isnullable = 1 THEN
                                                                 ''
                                                             ELSE
                                                                 'YES'
                                                         END,
                                               DefaultValue = e.text,
                                               FieldComment = ISNULL(g.[value], '')
                                        FROM syscolumns a
                                            LEFT JOIN systypes b
                                                ON a.xtype = b.xusertype
                                            INNER JOIN sysobjects d
                                                ON a.id = d.id
                                                   AND d.xtype = 'U'
                                                   AND d.name != 'dtproperties'
                                            LEFT JOIN syscomments e
                                                ON a.cdefault = e.id
                                            LEFT JOIN sys.extended_properties g
                                                ON a.id = g.major_id
                                                   AND a.colid = g.minor_id
                                            LEFT JOIN sys.extended_properties f
                                                ON d.id = f.major_id
                                                   AND f.minor_id = 0
                                            LEFT JOIN sys.identity_columns i
                                                ON i.[object_id] = OBJECT_ID(d.name)
                                                   AND i.name = a.name
                                        WHERE 1 = 1 @SqlWhere@
                                        ORDER BY d.name,
                                                 a.colorder;
                                     ";

        /// <summary>
        /// 设置表注释的SQL脚本
        /// </summary>
        public string SetTableCommentSQL = "EXECUTE sp_updateextendedproperty 'MS_Description',N'@Comment@','user','dbo','table','@DataTableName@',NULL,NULL";

        /// <summary>
        /// 设置列注释的SQL脚本
        /// </summary>
        public string SetColumnCommentSQL = "EXECUTE sp_updateextendedproperty 'MS_Description',N'@Comment@','user','dbo','table',N'@DataTableName@','column',N'@DataColumnName@'";

        /// <summary>
        /// 获取所有列
        /// </summary>
        /// <param name="listTableName">表名</param>
        /// <returns></returns>
        public List<Model.DkTableColumn> GetColumn(List<string> listTableName = null)
        {
            var whereSql = string.Empty;

            if (listTableName?.Count > 0)
            {
                listTableName.ForEach(x => x = x.Replace("'", ""));

                whereSql = "AND d.name in ('" + string.Join("','", listTableName) + "')";
            }

            var sql = GetColumnSQL.Replace("@SqlWhere@", whereSql);
            var ds = new DB.SQLServerDB().Query(sql);

            var list = ds.Tables[0].ToModel<Model.DkTableColumn>();

            return list;
        }

        /// <summary>
        /// 获取所有表
        /// </summary>
        /// <returns></returns>
        public List<Model.DkTableName> GetTable()
        {
            var ds = new DB.SQLServerDB().Query(GetTableSQL);

            var list = ds.Tables[0].ToModel<Model.DkTableName>();

            return list;
        }

        /// <summary>
        /// 设置表注释
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="TableComment">表注释</param>
        /// <returns></returns>
        public bool SetTableComment(string TableName, string TableComment)
        {
            var sql = SetTableCommentSQL.Replace("@DataTableName@", TableName.Replace("'", "")).Replace("@Comment@", TableComment.Replace("'", "''"));
            new DB.SQLServerDB().ExecuteSql(sql);
            return true;
        }

        /// <summary>
        /// 设置列注释
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="FieldName">列名</param>
        /// <param name="FieldComment">列注释</param>
        /// <returns></returns>
        public bool SetColumnComment(string TableName, string FieldName, string FieldComment)
        {
            var sql = SetColumnCommentSQL.Replace("@DataTableName@", TableName.Replace("'", "")).Replace("@DataColumnName@", FieldName.Replace("'", "")).Replace("@Comment@", FieldComment.Replace("'", "''"));
            new DB.SQLServerDB().ExecuteSql(sql);
            return true;
        }
    }
}